package dao

import (
	"context"
	"fmt"
	"gorm.io/gorm"
)

type ExperimentalPersonnelDAO struct {
	db *gorm.DB
}

func NewExperimentalPersonnelDap(db *gorm.DB) *ExperimentalPersonnelDAO {
	return &ExperimentalPersonnelDAO{
		db: db,
	}
}

func (dao *ExperimentalPersonnelDAO) Query(ctx context.Context, e ExperimentalPersonnel) ([]ExperimentalPersonnel, error) {
	var eps []ExperimentalPersonnel
	db := dao.db

	// 先获取exam score 的人员id
	// 再从
	if name := e.Name; name != "" {
		name = fmt.Sprintf("%%%v%%", name)
		db = db.WithContext(ctx).Where("exam_score.name like ?", name)
	}
	//if a.Chemicals == "" && a.Equipment == "" && a.Season == "" {
	//	return []Accident{}, nil
	//}
	//err := db.Table("exam_score").Debug().WithContext(ctx).Find(&eps).Error
	err := db.Table("people t1").Debug().
		Select("t1.id AS people_id, t1.name AS name, t1.class_name, t1.major_name, t1.college_name, count(t3.id) AS certificate_num").
		Joins("LEFT JOIN (SELECT t4.people_id, t4.module_id FROM exam_score t4 JOIN safety_learning_module t5 ON t4.module_id = t5.id WHERE t4.if_pass = '1') t2 ON t1.id = t2.people_id").
		Joins("LEFT JOIN certificate t3 ON t2.module_id = t3.id").
		Group("t1.id").
		Scan(&eps).Error

	//querySQl := fmt.Sprintf("select exam.*, category.name category_name, module.name module_name from exam_score exam join safety_learning_module module on exam.module_id = module.id join safety_learning_category category on module.category = category.code")
	//db.Raw(querySQl).Scan(&eps)

	if err != nil {
		fmt.Println("query error")
	}

	return eps, nil
}

func (dao *ExperimentalPersonnelDAO) QueryCertificate(ctx context.Context, PeopleId string) ([]Certificate, error) {
	var certificate []Certificate
	db := dao.db

	if PeopleId == "" {
		return certificate, nil
	}

	//err := db.Table("certificate").Debug().Where("id = ?", PeopleId).Find(&certificate).Error

	err := db.Table("people t1").Debug().
		Select("t3.id AS id, t3.name AS name, t3.obtainedTime AS obtainedTime, t3.location AS location, t3.department AS department").
		Joins("LEFT JOIN (SELECT t4.people_id, t4.module_id FROM exam_score t4 JOIN safety_learning_module t5 ON t4.module_id = t5.id WHERE t4.if_pass = '1') t2 ON t1.id = t2.people_id").
		Joins("LEFT JOIN certificate t3 ON t2.module_id = t3.id").
		Scan(&certificate).Error

	if err != nil {
		fmt.Println("query error")
	}

	return certificate, nil
}

type ExperimentalPersonnel struct {
	Id             string `gorm:"autoIncrement, primary_key"`
	PeopleId       string `gorm:"people_id"`
	Name           string `gorm:"name"`
	Age            uint
	ClassName      string `gorm:"column:class_name"`
	MajorName      string `gorm:"column:major_name"`
	CollegeName    string `gorm:"column:college_name"`
	PaperTestName  string `gorm:"column:paper_test_name"`
	Score          float64
	IfPassed       uint   `gorm:"column:if_passed"`
	ModuleName     string `gorm:"column:module_name"`
	CategoryName   string `gorm:"column:category_name"`
	CertificateNum string `gorm:"column:certificate_num"`
}

type Certificate struct {
	Id           string
	Name         string
	ObtainedTime string `gorm:"column:obtainedTime"`
	Location     string `gorm:"column:location"`
	Department   string `gorm:"column:department"`
}
